package com.perfma.xtest.tool;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.poi.hssf.usermodel.HSSFWorkbookFactory;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;

/**
 * @author: junhui.si
 * @date: 2023-11-13 18:11
 **/
public class SceneReportHourSectionExcelHelper {

    private static final String TEMPLATE = "/Users/sijunhui/Downloads/word/scene_report_hour_section_template.xlsx";

    public static void main(String[] args) {
        aa();
    }

    private static List<SceneReportHourSectionModel1> listModel1() {
        SceneReportHourSectionModel1 model1 = new SceneReportHourSectionModel1("1", "100", "10", "10%");
        SceneReportHourSectionModel1 model2 = new SceneReportHourSectionModel1("2", "200", "20", "20%");
        SceneReportHourSectionModel1 model3 = new SceneReportHourSectionModel1("3", "300", "30", "30%");
        return Lists.newArrayList(model1, model2, model3);
    }

    private static List<SceneReportHourSectionModel2> listModel2() {
        SceneReportHourSectionModel2 model1 = new SceneReportHourSectionModel2("综合资源", "ip1", "CPU", "{.machineCpuList_1}");
        SceneReportHourSectionModel2 model2 = new SceneReportHourSectionModel2("综合资源", "ip1", "内存", "{.machineMemList_1}");
        SceneReportHourSectionModel2 model3 = new SceneReportHourSectionModel2("综合资源", "ip1", "磁盘", "{.machineDiskList_1}");
        SceneReportHourSectionModel2 model4 = new SceneReportHourSectionModel2("综合资源", "ip1", "网络", "{.machineNetList_1}");

        SceneReportHourSectionModel2 model5 = new SceneReportHourSectionModel2("综合资源", "ip2", "CPU", "{.machineCpuList_2}");
        SceneReportHourSectionModel2 model6 = new SceneReportHourSectionModel2("综合资源", "ip2", "内存", "{.machineMemList_2}");
        SceneReportHourSectionModel2 model7 = new SceneReportHourSectionModel2("综合资源", "ip2", "磁盘", "{.machineDiskList_2}");
        SceneReportHourSectionModel2 model8 = new SceneReportHourSectionModel2("综合资源", "ip2", "网络", "{.machineNetList_2}");
        return Lists.newArrayList(model1, model2, model3, model4, model5, model6, model7, model8);
    }

    private static List<Map<String, String>> listModel3() {
        Map<String, String> model1 = Maps.newHashMap();
        model1.put("machineCpuList_1", "cpu1");
        model1.put("machineMemList_1", "mem1");
        model1.put("machineDiskList_1", "disk1");
        model1.put("machineNetList_1", "net1");

        model1.put("machineCpuList_2", "cpu2");
        model1.put("machineMemList_2", "mem2");
        model1.put("machineDiskList_2", "disk2");
        model1.put("machineNetList_2", "net2");

        Map<String, String> model2 = Maps.newHashMap();
        model2.put("machineCpuList_1", "cpu1");
        model2.put("machineMemList_1", "mem1");
        model2.put("machineDiskList_1", "disk1");
        model2.put("machineNetList_1", "net1");

        model2.put("machineCpuList_2", "cpu2");
        model2.put("machineMemList_2", "mem2");
        model2.put("machineDiskList_2", "disk2");
        model2.put("machineNetList_2", "net2");

        Map<String, String> model3 = Maps.newHashMap();
        model3.put("machineCpuList_1", "cpu1");
        model3.put("machineMemList_1", "mem1");
        model3.put("machineDiskList_1", "disk1");
        model3.put("machineNetList_1", "net1");

        model3.put("machineCpuList_2", "cpu2");
        model3.put("machineMemList_2", "mem2");
        model3.put("machineDiskList_2", "disk2");
        model3.put("machineNetList_2", "net2");

        return Lists.newArrayList(model1, model2, model3);
    }

    private static List<Map<String, String>> listName() {
        Map<String, String> model1 = Maps.newHashMap();
        model1.put("transactionName", "事务1");

        Map<String, String> model2 = Maps.newHashMap();
        model2.put("transactionName", "事务2");

        Map<String, String> model3 = Maps.newHashMap();
        model3.put("transactionName", "事务3");

        return Lists.newArrayList(model1, model2, model3);
    }

    private static List<Map<String, String>> listTpsName() {
        Map<String, String> model1 = Maps.newHashMap();
        model1.put("tpsTitle", "TPS（笔/秒）");
        model1.put("tpsData", "{.tpsData_1}");

        Map<String, String> model2 = Maps.newHashMap();
        model2.put("tpsTitle", "TPS（笔/秒）");
        model2.put("tpsData", "{.tpsData_2}");

        Map<String, String> model3 = Maps.newHashMap();
        model3.put("tpsTitle", "TPS（笔/秒）");
        model3.put("tpsData", "{.tpsData_3}");

        return Lists.newArrayList(model1, model2, model3);
    }

    private static List<Map<String, String>> listTpsData() {
        Map<String, String> model1 = Maps.newHashMap();
        model1.put("tpsData_1", "tps1");
        model1.put("tpsData_2", "tps2");
        model1.put("tpsData_3", "tps3");

        Map<String, String> model2 = Maps.newHashMap();
        model2.put("tpsData_1", "tps1");
        model2.put("tpsData_2", "tps2");
        model2.put("tpsData_3", "tps3");

        Map<String, String> model3 = Maps.newHashMap();
        model3.put("tpsData_1", "tps1");
        model3.put("tpsData_2", "tps2");
        model3.put("tpsData_3", "tps3");

        return Lists.newArrayList(model1, model2, model3);
    }

    private static void aa() {
        String fileName = "/Users/sijunhui/Downloads/word/complexFill_" + System.currentTimeMillis() + ".xlsx";
        // 方案1
        try {
            ByteArrayOutputStream templateOut = new ByteArrayOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(templateOut).withTemplate(TEMPLATE).build();
//            ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(TEMPLATE).build();
            WriteSheet writeSheet1 = EasyExcel.writerSheet(0).build();
            WriteSheet writeSheet2 = EasyExcel.writerSheet(1).build();
            WriteSheet writeSheet3 = EasyExcel.writerSheet(2).build();
            WriteSheet writeSheet4 = EasyExcel.writerSheet(3).build();

            Map<String, Object> map = Maps.newHashMap();
            map.put("title", "这是标题");
            map.put("users", 200);
            map.put("time", "2023-01-01 00:00:00");
            excelWriter.fill(map, writeSheet1);

            FillConfig fillConfig1 = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).forceNewRow(Boolean.TRUE).build();
            FillConfig fillConfig2 = FillConfig.builder().forceNewRow(Boolean.TRUE).direction(WriteDirectionEnum.VERTICAL).build();
            excelWriter.fill(listModel1(), fillConfig1, writeSheet1);
            excelWriter.fill(listModel1(), fillConfig1, writeSheet2);
            excelWriter.fill(listModel1(), fillConfig1, writeSheet3);
            excelWriter.fill(listModel1(), fillConfig1, writeSheet4);

            excelWriter.fill(listTpsName(), fillConfig2, writeSheet2);

            excelWriter.fill(listName(), fillConfig2, writeSheet2);
            excelWriter.fill(listName(), fillConfig2, writeSheet3);
            excelWriter.fill(listName(), fillConfig2, writeSheet4);


            excelWriter.fill(listModel2(), fillConfig2, writeSheet1);

            excelWriter.finish();

            //将第一次模板的输出流转成输入流作为第二次导出的模版
            byte[] bytes = templateOut.toByteArray();
            ByteArrayInputStream tempIn = new ByteArrayInputStream(bytes);

            ByteArrayOutputStream templateOut2 = new ByteArrayOutputStream();
            ExcelWriter excelWriter2 = EasyExcel.write(templateOut2).withTemplate(tempIn).build();
            writeSheet1 = EasyExcel.writerSheet(0).build();
            writeSheet2 = EasyExcel.writerSheet(1).build();

            excelWriter2.fill(listModel3(), fillConfig1, writeSheet1);
            excelWriter2.fill(listTpsData(), fillConfig1, writeSheet2);

            //省略模版填充代码
            excelWriter2.finish();


            // 3.打开Excel文件
            byte[] bytes3 = templateOut2.toByteArray();
            ByteArrayInputStream tempIn3 = new ByteArrayInputStream(bytes3);

            Workbook workbook = HSSFWorkbookFactory.create(tempIn3);
            Sheet sheet1 = workbook.getSheetAt(0);

            CellRangeAddress region;
            // 合并单元格
            region = new CellRangeAddress(5, 12, 0, 0);
            sheet1.addMergedRegion(region);
            region = new CellRangeAddress(5, 8, 1, 1);
            sheet1.addMergedRegion(region);
            region = new CellRangeAddress(9, 12, 1, 1);
            sheet1.addMergedRegion(region);

            Sheet sheet2 = workbook.getSheetAt(1);
//            // 补边框
//            region = new CellRangeAddress(2, 2, 1, 2);
//            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet2);
//            sheet2.addMergedRegion(region);
//
//            region = new CellRangeAddress(3, 3, 1, 2);
//            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet2);
//            sheet2.addMergedRegion(region);

            copySheetData(sheet2, sheet1, 0, 3, 0, 5);

            // 合并单元格+补边框
            region = new CellRangeAddress(15, 15, 1, 2);
            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet1);
            sheet1.addMergedRegion(region);
            region = new CellRangeAddress(16, 16, 1, 2);
            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet1);
            sheet1.addMergedRegion(region);
            region = new CellRangeAddress(17, 17, 1, 2);
            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet1);
            sheet1.addMergedRegion(region);
            region = new CellRangeAddress(18, 18, 1, 2);
            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet1);
            sheet1.addMergedRegion(region);

//            workbook.removeSheetAt(1);
//            workbook.removeSheetAt(1);
//            workbook.removeSheetAt(1);

            FileOutputStream fout = new FileOutputStream(fileName);
            workbook.write(fout);
            fout.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    private static void copySheetData(Sheet sourceSheet, Sheet targetSheet, int startRow, int endRow, int startCol, int endCol) {
        for (int i = startRow; i <= endRow; i++) {
            // 创建行
            Row sourceRow = sourceSheet.getRow(i);
            Row targetRow = targetSheet.getRow(15 + i);
            if (targetRow == null) {
                targetRow = targetSheet.createRow(15 + i);
            }

            for (int j = startCol; j <= endCol; j++) {
                // 创建单元格
                Cell sourceCell = sourceRow.getCell(j);
                Cell targetCell = targetRow.getCell(j);
                if (targetCell == null) {
                    targetCell = targetRow.createCell(j);
                }

                // 复制单元格值和样式
                copyCellValueAndStyle(sourceCell, targetCell);
            }
        }
    }

    private static void copyCellValueAndStyle(Cell sourceCell, Cell targetCell) {
        if (sourceCell != null) {
            // 复制值
            targetCell.setCellValue(sourceCell.getStringCellValue());

            // 复制样式
            targetCell.setCellStyle(sourceCell.getCellStyle());
        }
    }
}


